﻿using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using MimeKit.Cryptography;
using Quartz;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Reflection.Emit;
using VOL.Core.Configuration;
using VOL.Core.DBManager;
using VOL.Core.Enums;
using VOL.Core.Extensions;
using VOL.Entity.DomainModels;

namespace VOL.WebApi.Controllers.BigScreen
{
    [Route("api/wholeVheiclePaymentback")]
    [ApiController]
    public class WholeVehiclePaymentback:Controller
    {
        [HttpPost,Route("accountAuthorization"),AllowAnonymous]
        public int AccountAuthorization([FromBody] UserInfo info)
        {
            List<Sys_User> user = DBServerProvider.SqlDapper.QueryList<Sys_User>("select * from Sys_User where username = '" + info.username + "'",null).ToList();
            if(user.Count==0 || info.password.Trim().EncryptDES(AppSetting.Secret.User) != (user[0].UserPwd ?? ""))
            {
                return -1;
            }
            int userid = user[0].User_Id;
            List<Sys_UserRole> user_role = DBServerProvider.SqlDapper.QueryList<Sys_UserRole>("select * from Sys_UserRole where userid = '" + userid + "' and roleid=1070",null).ToList();
            if(user_role.Count==0)
            {
                return 0;
            }
            return 1;
        }

        //查询所有部门
        [HttpPost,Route("getDepartmentInfo"),AllowAnonymous]
        public List<dynamic> GetDepartmentInfo()
        {
            List<dynamic> result = new List<dynamic>();
            try
            {
                string sql = "select * from openquery([DMS],'select (case when A.department is null then ''未确认'' else (select tcc.code_name from TM_CHNG_CODE tcc where tcc.code=A.DEPARTMENT)end) value,(case when A.department is null then ''未确认'' else (select tcc.code_name from TM_CHNG_CODE tcc where tcc.code=A.DEPARTMENT) end) label from tt_q_fund_money A group by department order by value')";
                result=DBServerProvider.SqlDapper.QueryList<dynamic>(sql, null).ToList();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return result;
        }

        //查询所有年份
        [HttpPost,Route("getYearInfo"),AllowAnonymous]
        public List<dynamic> GetYearInfo()
        {
            List<dynamic> result = new List<dynamic>();
            try
            {
                string sql = "select * from openquery([DMS],'select distinct TO_CHAR(A.GET_MONEY_DATE, ''YYYY'') as value,TO_CHAR(A.GET_MONEY_DATE, ''YYYY'') as label from tt_q_fund_money A group by get_money_date') order by value asc";
                result = DBServerProvider.SqlDapper.QueryList<dynamic>(sql, null).ToList();
            }catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return result;
        }

        //根据部门查询付款情况
        [HttpPost,Route("getPaymentInfo"),AllowAnonymous]
        public List<dynamic> GetPaymentInfo(string department)
        {
            List<dynamic> result = new List<dynamic>();
            try
            {
                string sql=null;
                if (department == "汇总")
                {
                    sql = "select * from openquery([DMS],'select (case when A.pay_type is null then ''未确认'' else f_get_codename(A.PAY_TYPE) end) fu_type,round((sum(money)/10000),2) money from tt_q_fund_money A group by PAY_TYPE')";
                }
                else
                {
                    sql = "select * from openquery([DMS],'select (case when A.pay_type is null then ''未确认'' else f_get_codename(A.PAY_TYPE) end) fu_type,(case when A.department is null then ''未确认'' else (select tcc.code_name from TM_CHNG_CODE tcc where tcc.code=A.DEPARTMENT) end) name,round((sum(money)/10000),2) money from tt_q_fund_money A group by PAY_TYPE,department') where name ='" + department + "' order by name asc";
                }
                result = DBServerProvider.SqlDapper.QueryList<dynamic>(sql, null).ToList();
            }
            catch(Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return result;
        }

        //根据部门和年份查询每月收款汇总情况
        [HttpPost,Route("getPaymentbackMonthInfo"),AllowAnonymous]
        public List<dynamic> GetPaymentbackMonthInfo(string department,string year)
        {
            List<dynamic> result = new List<dynamic>();
            try
            {
                string sql = null;
                if (department == "汇总")
                {
                    sql = "select * from openquery([DMS],'select round((sum(money)/10000),2) money,TO_CHAR(A.GET_MONEY_DATE, ''YYYY-MM'') month from tt_q_fund_money A where TO_CHAR(A.GET_MONEY_DATE, ''YYYY'')=" + year + " group by TO_CHAR(A.GET_MONEY_DATE, ''YYYY-MM'')') order by month asc";
                }
                else
                {
                    sql = "select * from openquery([DMS],'select round((sum(money)/10000),2) money,TO_CHAR(A.GET_MONEY_DATE, ''YYYY-MM'') month,(case when A.department is null then ''未确认'' else (select tcc.code_name from TM_CHNG_CODE tcc where tcc.code=A.DEPARTMENT) end) name from tt_q_fund_money A where TO_CHAR(A.GET_MONEY_DATE, ''YYYY'')=" + year + " group by TO_CHAR(A.GET_MONEY_DATE, ''YYYY-MM''),department') where name='" + department + "' order by month asc";
                }
                result = DBServerProvider.SqlDapper.QueryList<dynamic>(sql, null).ToList();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return result;
        }

        //根据部门查询款项使用情况
        [HttpPost, Route("getMoneyUseInfo"), AllowAnonymous]
        public List<dynamic> GetMoneyUseInfo(string department)
        {
            List<dynamic> result = new List<dynamic>();
            string sql = null;
            try
            {
                if (department == "汇总")
                {
                    sql = "select * from openquery([DMS], 'select (case when D.use_name is null then ''未确认'' else D.use_name end) use_name,(case when round((sum(B.to_amount)/10000),2) is null then round((sum(A.money)/10000),2) else round((sum(B.to_amount)/10000),2) end) money from tt_q_fund_money A left join tt_q_fund_money_to B on A.fund_id=B.fund_id and B.is_valid=10011001 left join TM_Q_USE D on B.use_id=D.use_id where 1=1 group by D.use_name order by money desc')";
                }
                else
                {
                    sql = "select * from openquery([DMS], 'select (case when A.department is null then ''未确认'' else (select tcc.code_name from TM_CHNG_CODE tcc where tcc.code=A.DEPARTMENT) end) department, (case when D.use_name is null then ''未确认'' else D.use_name end) use_name,(case when round((sum(B.to_amount)/10000),2) is null then round((sum(A.money)/10000),2) else round((sum(B.to_amount)/10000),2) end) money from tt_q_fund_money A left join tt_q_fund_money_to B on A.fund_id=B.fund_id and B.is_valid=10011001 left join TM_Q_USE D on B.use_id=D.use_id where 1=1 group by A.department,D.use_name order by A.department, money desc') where department='" + department+"'";
                }
                result= DBServerProvider.SqlDapper.QueryList<dynamic>(sql, null).ToList();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return result;
        }

        //根据部门查询回款明细表
        [HttpPost,Route("getPaymentbackSchedule"),AllowAnonymous]
        public List<dynamic> GetPaymentbackSchedule (string department)
        {
            List<dynamic> result = new List<dynamic>();
            string sql = null;
            try
            {
                if (department == "汇总")
                {
                    sql = "select * from openquery([DMS],'select to_char(A.get_money_date,''YYYY-MM-DD'') get_money_date,(case when A.department is null then ''未确认'' else (select tcc.code_name from TM_CHNG_CODE tcc where tcc.code=A.DEPARTMENT) end) department,f_get_codename(A.PAY_TYPE) pay_type,A.money,A.fk_name,C.bank_name,A.dealer_name,D.type payment_type,D.use_name money_use,B.to_amount money_schedule FROM TT_Q_FUND_MONEY A left join TT_Q_FUND_MONEY_TO B on A.FUND_ID=B.FUND_ID AND B.IS_VALID=10011001 left join TM_Q_BANK C on A.BANK_ID=C.BANK_ID left join TM_Q_USE D on B.USE_ID=D.USE_ID') order by get_money_date desc";
                }
                else
                {
                    sql = "select * from openquery([DMS],'select to_char(A.get_money_date,''YYYY-MM-DD'') get_money_date,(case when A.department is null then ''未确认'' else (select tcc.code_name from TM_CHNG_CODE tcc where tcc.code=A.DEPARTMENT) end) department,f_get_codename(A.PAY_TYPE) pay_type,A.money,A.fk_name,C.bank_name,A.dealer_name,D.type payment_type,D.use_name money_use,B.to_amount money_schedule FROM TT_Q_FUND_MONEY A left join TT_Q_FUND_MONEY_TO B on A.FUND_ID=B.FUND_ID AND B.IS_VALID=10011001 left join TM_Q_BANK C on A.BANK_ID=C.BANK_ID left join TM_Q_USE D on B.USE_ID=D.USE_ID') where department = '" + department + "' order by get_money_date desc";
                }
                result=DBServerProvider.SqlDapper.QueryList<dynamic>(sql, null).ToList();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return result;
        }
    }

    public class UserInfo
    {
        public string username { get; set; }
        public string password { get; set; }
    }
}
